mysql 实现 connect by start with |
您所在的位置：网站首页 › hive connect by prior › mysql 实现 connect by start with |
mysql 实现 connect by start with
|
一 前言 1、mysql没有层级查询方法 而 oracle通过connect by start with语法可以实现层级查询 2、mysql实现层级查询的方式很多，有使用存储过程函数嵌套调用亦有使用临时表进行层级查询 3、本文使用一种变量循环赋值方式进行，可以套用模版 二 实验 -- 创建测试表 -- DROP TABLE IF EXISTS `test_tree`; CREATE TABLE `test_tree` ( `id` varchar(10) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `pId` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 创建测试数据（根节点默认-1） INSERT INTO `test_tree` VALUES ('1', '中国', '-1'); INSERT INTO `test_tree` VALUES ('2', '福建省', '1'); INSERT INTO `test_tree` VALUES ('3', '海南省', '1'); INSERT INTO `test_tree` VALUES ('4', '泉州市', '2'); INSERT INTO `test_tree` VALUES ('5', '福州市', '2'); INSERT INTO `test_tree` VALUES ('6', '泉港区', '4'); INSERT INTO `test_tree` VALUES ('7', '惠安县', '4'); -- 模版 表名代替test_tree 用id替换以下id 用pid替换以下 用其他从属字段替换name SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id -- 结合instr(paths,'想要查所有子集的父级id')>0 验证 SELECT name, id, pid, levels, paths FROM ( SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id ) src WHERE instr(paths,'-1')>0 ORDER BY pid三 验证结果 1、数据
2 查询中国 SELECT name, id, pid, levels, paths FROM ( SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id ) src WHERE instr(paths,'1')>0 ORDER BY pid
3 查询福建省 SELECT name, id, pid, levels, paths FROM ( SELECT name, id, pid, @le:= IF (pid = -1 ,0,IF( LOCATE( CONCAT('|',pid,':'),@pathlevel) > 0,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',pid,':'),-1),'|',1) +1,@le+1) ) levels, @pathlevel:= CONCAT(@pathlevel,'|',id,':', @le ,'|') pathlevel, @pathnodes:= IF( pid =-1,',root', CONCAT_WS(',',IF( LOCATE( CONCAT('|',pid,':'),@pathall) > 0 ,SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',pid,':'),-1),'|',1),@pathnodes ) ,pid ) )paths, @pathall:=CONCAT(@pathall,'|',id,':', @pathnodes ,'|') pathall FROM test_tree, ( SELECT @le:=0, @pathlevel:='', @pathall:='', @pathnodes:='' ) vv ORDER BY pid,id ) src WHERE instr(paths,'2')>0 ORDER BY pid
|
【本文地址】